{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Introduction","Bootstrap","Forward rates","Immunisation","IRS","PCA (soon)"],"not_rendered":{"charts":0,"images":174,"macros":1,"external_links":0,"defined_names":11,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":10,"n_cols":8,"rows_kept":10,"cut":false,"hidden":false,"merged":[[3,3,3,5]]}
[null,null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\",A1), LEN(CELL(\"filename\",A1)) - SEARCH(\"]\", CELL(\"filename\",A1)))","v":"Introduction"}]
[null,null,null,null,null,null,"by Hugues Pirotte"]
[]
[null,null,null,"Below is a list of the worksheets\nof the file, by order of appearance:\n(CTRL+h = back to \"Introduction\")",null,null,null,{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]
[null,null,1,"Introduction"]
[null,null,2,"Bootstrap"]
[null,null,3,"Forward rates"]
[null,null,4,"Immunisation"]
[null,null,5,"IRS"]
[null,null,6,"PCA (soon)"]

{"sheet":"Bootstrap","index":1,"n_rows":43,"n_cols":9,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
[{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]
[]
[null,1,null,"We want to have a 0-coupon \"benchmark\" term-structure"]
[null,null,null,"to discount our cash flows…."]
[null,null,"a","Goal"]
[null,null,null,"Term","Rate"]
[null,null,null,"1m"]
[null,null,null,"2m"]
[null,null,null,"3m"]
[null,null,null,"6m"]
[null,null,null,"12m"]
[null,null,null,"2y"]
[null,null,null,"…"]
[]
[null,2,"How to obtain them?"]
[null,null,"a","Extract them from coupon-bonds…"]
[null,null,null,"(bootstrapping)",null,null,null,null,"Market"]
[null,null,null,null,"Year1","Year2","Year3","Year4","Prices"]
[null,null,null,1,103,null,null,null,"x"]
[null,null,null,2,5,105,null,null,"y"]
[null,null,null,3,4,4,104,null,"z"]
[null,null,null,4,6,6,6,106,"w"]
[]
[null,null,null,1]
[]
[null,null,null,2]
[]
[null,null,null,3]
[]
[null,null,null,4]
[]
[null,null,"b"]
[null,null,null,null,"1m"]
[null,null,null,null,"3m"]
[null,null,null,null,"4m"]
[null,null,null,null,"6m"]
[null,null,null,null,"12m"]
[null,null,null,null,"2y"]
[null,null,null,null,"3y"]
[null,null,null,null,"5y"]
[null,null,null,null,"10y"]
[null,null,null,null,"20y"]
[null,null,null,null,"30y"]

{"sheet":"Forward rates","index":2,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
[{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]
[]
[null,null,null,"Year","Spot rate","Forward rate"]
[null,null,null,1,0.02]
[null,null,null,2,2.5000000000000001E-2]
[null,null,null,3,3.2500000000000001E-2]
[null,null,null,4,3.5999999999999997E-2]
[null,null,null,5,5.1999999999999998E-2,{"$f":"E8+4*(E8-E7)","v":0.11599999999999999}]
[null,null,null,null,null,{"$f":"(1+E8)^D8/(1+E7)^D7-1","v":0.11850950089295464}]

{"sheet":"Immunisation","index":3,"n_rows":54,"n_cols":29,"rows_kept":54,"cut":false,"hidden":false,"merged":[[24,18,24,19],[24,25,24,26],[15,3,15,5],[15,6,15,8],[24,4,24,5],[24,11,24,12]]}
[{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]
[]
[null,1,"Exercise",null,null,null,null,"Immunisation","= replicating a 0-coupon bond with coupon-paying bonds"]
[]
[null,null,null,"Commitment with a client",null,null,3,"years investment"]
[null,null,null,"Current structure of IRs",null,null,0.04,"flat"]
[]
[null,null,null,"There is no 3 years 0-coupon bond"]
[null,null,null,"You have two bonds in the market instead"]
[null,null,null,null,"coupon","maturity"]
[null,null,null,"A-bond",3,2]
[null,null,null,"B-bond",6,5]
[]
[null,null,null,"For the bank, the problem is the following:"]
[]
[null,null,null,"Assets",null,null,"Liabilities"]
[null,null,null,"A-bond","?","EUR","3y straight bond",10000,"EUR"]
[null,null,null,"B-bond","?","EUR"]
[]
[null,null,null,"duration",3,null,"duration",3]
[null,null,null,null,null,null,"amount",null,"expected at T=3"]
[]
[null,null,null,"Solution"]
[]
[null,null,null,null,"Year 0",null,"rate",0.04,null,null,null,"Year 1",null,"rate",0.04,null,null,null,"Year 2",null,"rate",0.04,null,null,null,"Year 3",null,"rate",0.04]
[]
[null,null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond"]
[null,null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)"]
[null,null,null,0,null,null,null,null,null,null,0,null,null,null,null,null,null,0,null,null,null,null,null,null,0]
[null,null,null,1]
[null,null,null,2,null,null,null,null,null,null,1]
[null,null,null,3,null,null,null,null,null,null,2,null,null,null,null,null,null,1]
[null,null,null,4,null,null,null,null,null,null,3,null,null,null,null,null,null,2,null,null,null,null,null,null,1]
[null,null,null,5,null,null,null,null,null,null,4,null,null,null,null,null,null,3,null,null,null,null,null,null,2]
[]
[null,null,null,null,"Duration",null,"Duration",null,null,null,null,"Duration",null,"Duration",null,null,null,null,"Duration",null,"Duration"]
[null,null,null,null,"Weights",null,null,null,null,null,null,"Weights",null,null,null,null,null,null,"Weights"]
[]
[null,null,null,null,"Desired duration",null,null,null,null,null,null,"Desired duration",null,null,null,null,null,null,"Desired duration",null,null,null,null,null,null,"Desired duration"]
[null,null,null,null,"Effective duration",null,null,null,null,null,null,"Effective duration",null,null,null,null,null,null,"Effective duration",null,null,null,null,null,null,"Effective duration"]
[null,null,null,null,"Amount available?",null,null,null,null,null,null,"Amount available?",null,null,null,null,null,null,"Amount available?",null,null,null,null,null,null,"Amount available?",{"$f":"10000*(1+4%)^3","v":11248.640000000001}]
[]
[null,null,null,null,"Allocation",null,"EUR",null,"EUR",null,null,"Allocation",null,"EUR",null,"EUR",null,null,"Allocation",null,"EUR",null,"EUR",null,null,"Average yearly return over 3y"]
[null,null,null,null,"# bonds of 1000?",null,null,null,null,null,null,"# bonds of 1000?",null,null,null,null,null,null,"# bonds of 1000?"]
[]
[]
[null,2,null,"Stress-test"]
[null,null,null,null,null,"Rate at year 2"]
[null,null,null,null,null,0.02,2.5000000000000001E-2,0.04,0.05,0.06]
[null,null,null,"rate at year 1",0.02]
[null,null,null,null,0.03]
[null,null,null,null,0.04]
[null,null,null,null,0.05]
[null,null,null,null,0.06]

{"sheet":"IRS","index":4,"n_rows":94,"n_cols":10,"rows_kept":94,"cut":false,"hidden":false,"merged":[]}
[{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]
[]
[null,1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,2]
[null,null,null,"Companies","Rating","Fixed","Floating","Objective"]
[null,null,null,"A","AAA",0.04,"LIBOR+1%","borrowing floating"]
[null,null,null,"B","BBB",0.06,"LIBOR+2%","borrowing fixed"]
[null,null,null,null,"difference",0.02,0.01]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,3]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,4,"Swap valuation"]
[null,null,null,null,"Floating",null,"Fixed"]
[null,null,null,"Year","leg",null,"leg"]
[null,null,null,1,"-L",null,3.7999999999999999E-2]
[null,null,null,2,"-L",null,3.7999999999999999E-2]
[null,null,null,3,"-L",null,3.7999999999999999E-2]
[null,null,null,4,"-L",null,3.7999999999999999E-2]
[null,null,null,5,"-L",null,3.7999999999999999E-2]
[null,null,null,6,"-L",null,3.7999999999999999E-2]
[null,null,null,7,"-L",null,3.7999999999999999E-2]
[null,null,null,8,"-L",null,3.7999999999999999E-2]
[null,null,null,9,"-L",null,3.7999999999999999E-2]
[null,null,null,10,"-L","-100",3.7999999999999999E-2,"+100"]
[]
[]
[null,5,null,"Imagine a 5-year Swap floating vs. ? fixed"]
[]
[null,null,"a",null,"coupon",4.1154590698566196E-2]
[]
[null,null,null,null,"Spot","E(payments)","PV(CFs)",null,"E(payments)","PV(CFs)"]
[null,null,null,"Year","rates","fixed leg","fixed leg",null,"floating leg","floating leg"]
[null,null,null,1,0.02,{"$f":"$F$67","v":4.1154590698566196E-2},{"$f":"F71/(1+E71)^D71","v":4.0347637939770781E-2},null,{"$f":"E71","v":0.02},{"$f":"I71/(1+E71)^D71","v":1.9607843137254902E-2}]
[null,null,null,2,2.5000000000000001E-2,{"$f":"$F$67","v":4.1154590698566196E-2},{"$f":"F72/(1+E72)^D72","v":3.917153189631524E-2},null,{"$f":"(1+E72)^D72/(1+E71)^D71-1","v":3.0024509803921573E-2},{"$f":"I72/(1+E72)^D72","v":2.8577760670002689E-2}]
[null,null,null,3,3.2500000000000001E-2,4.1154590698566196E-2,3.7389366411758627E-2,null,{"$f":"(1+E73)^D73/(1+E72)^D72-1","v":4.7665035693039703E-2},4.3304172251643958E-2]
[null,null,null,4,3.7499999999999999E-2,4.1154590698566196E-2,3.5519419977231612E-2,null,5.2645748036585838E-2,4.5437128708758046E-2]
[null,null,null,5,4.2000000000000003E-2,{"$f":"$F$67+1","v":1.0411545906985662},0.8475720437749239,null,{"$f":"(1+E75)^D75/(1+E74)^D74","v":1.0601960291274033},0.86307309523234044]
[null,null,null,null,null,null,{"$f":"SUM(G71:G75)","v":1.0000000000000002},null,null,{"$f":"SUM(J71:J75)","v":1}]
[]
[null,null,null,null,null,"Swap value",{"$f":"G76-J76","v":0}]
[]
[null,null,"b","Next year…"]
[]
[null,null,null,null,"coupon",4.1154590698566196E-2]
[]
[null,null,null,null,"Spot","E(payments)","PV(CFs)",null,"E(payments)","PV(CFs)"]
[null,null,null,"Year","rates","fixed leg","fixed leg",null,"floating leg","floating leg"]
[null,null,null,1,2.75E-2,{"$f":"$F$82","v":4.1154590698566196E-2},{"$f":"F86/(1+E86)^D86","v":4.0053129633641063E-2},null,{"$f":"E86","v":2.75E-2},{"$f":"I86/(1+E86)^D86","v":2.6763990267639901E-2}]
[null,null,null,2,3.5999999999999997E-2,4.1154590698566196E-2,3.8344120073648083E-2,null,{"$f":"(1+E87)^D87/(1+E86)^D86-1","v":4.457031630170305E-2},4.1526583814439862E-2]
[null,null,null,3,0.04,4.1154590698566196E-2,3.6586281273617245E-2,null,4.8046391675735434E-2,4.2713067247005354E-2]
[null,null,null,4,0.05,{"$f":"$F$82+1","v":1.0411545906985662},0.85656045840863937,null,{"$f":"(1+E89)^D89/(1+E88)^D88","v":1.0805806301917387},0.88899635867091487]
[null,null,null,null,null,null,{"$f":"SUM(G86:G89)","v":0.97154398938954578},null,null,{"$f":"SUM(J86:J89)","v":1}]
[]
[null,null,null,null,null,"Swap value",{"$f":"G90-J90","v":-2.8456010610454219E-2}]
[]
[null,6,"An example"]

{"sheet":"PCA (soon)","index":5,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
[{"$f":"MID(CELL(\"filename\",A1),SEARCH(\"-\",CELL(\"filename\",A1))+7,SEARCH(\".\",CELL(\"filename\",A1))-SEARCH(\"-\",CELL(\"filename\",A1))-7)","v":"Interest rate risk management"}]